import sqlite3
from flask import Blueprint, render_template, request, redirect, url_for, flash, g
from database import get_db
from flask import current_app  # 推荐使用current_app而非直接导入app

inventory_category_bp = Blueprint('inventory_category', __name__)

# 获取所有分类（递归查询）
def get_all_inventory_categories():
    db = get_db()
    cursor = db.cursor()
    # 使用递归CTE查询分类层级结构并生成hierarchy_path
    query = """
    WITH RECURSIVE category_tree AS (
        SELECT 
            id, name, parent_id, level,
            CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
        FROM inventory_categories
        WHERE parent_id IS NULL OR parent_id = 0
        UNION ALL
        SELECT 
            c.id, c.name, c.parent_id, c.level,
            ct.hierarchy_path || '.' || printf('%03d', c.id) AS hierarchy_path
        FROM inventory_categories c
        JOIN category_tree ct ON c.parent_id = ct.id
    )
    SELECT * FROM category_tree ORDER BY hierarchy_path;
    """
    cursor.execute(query)
    categories = cursor.fetchall()
    return [dict(id=row[0], name=row[1], parent_id=row[2], level=row[3]) for row in categories]

# 获取分类详情
def get_inventory_category(category_id):
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT * FROM inventory_categories WHERE id = ?', (category_id,))
    row = cursor.fetchone()
    if row:
        return dict(id=row[0], name=row[1], parent_id=row[2], level=row[3])
    return None

# 获取子分类数量
def get_subcategory_count(category_id):
    db = get_db()
    cursor = db.cursor()
    cursor.execute('SELECT COUNT(*) FROM inventory_categories WHERE parent_id = ?', (category_id,))
    return cursor.fetchone()[0]

# 目录列表页
@inventory_category_bp.route('/inventory_categories')
def category_list():
    search_query = request.args.get('search', '').strip()
    db = get_db()
    cursor = db.cursor()
    
    # 构建查询（包含搜索条件和子分类计数）
    if search_query:
        query = """WITH RECURSIVE category_hierarchy AS (
            SELECT 
                id, 
                name, 
                parent_id, 
                level,
                CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
            FROM inventory_categories
            WHERE parent_id IS NULL OR parent_id = 0
            
            UNION ALL
            
            SELECT 
                c.id, 
                c.name, 
                c.parent_id, 
                c.level,
                ch.hierarchy_path || '.' || printf('%03d', c.id) AS hierarchy_path
            FROM inventory_categories c
            JOIN category_hierarchy ch ON c.parent_id = ch.id
        )
        SELECT c.id, c.name, c.parent_id, c.level, COUNT(child.id) as child_count 
        FROM inventory_categories c
        LEFT JOIN inventory_categories child ON c.id = child.parent_id
        JOIN category_hierarchy ch ON c.id = ch.id
        WHERE c.name LIKE ?
        GROUP BY c.id, ch.hierarchy_path
        ORDER BY ch.hierarchy_path"""
        params = [f'%{search_query}%']
    else:
        query = """WITH RECURSIVE category_hierarchy AS (
            SELECT 
                id, 
                name, 
                parent_id, 
                level,
                CAST(printf('%03d', id) AS TEXT) AS hierarchy_path
            FROM inventory_categories
            WHERE parent_id IS NULL OR parent_id = 0
            
            UNION ALL
            
            SELECT 
                c.id, 
                c.name, 
                c.parent_id, 
                c.level,
                ch.hierarchy_path || '.' || printf('%03d', c.id) AS hierarchy_path
            FROM inventory_categories c
            JOIN category_hierarchy ch ON c.parent_id = ch.id
        )
        SELECT c.id, c.name, c.parent_id, c.level, COUNT(child.id) as child_count 
        FROM inventory_categories c
        LEFT JOIN inventory_categories child ON c.id = child.parent_id
        JOIN category_hierarchy ch ON c.id = ch.id
        GROUP BY c.id, ch.hierarchy_path
        ORDER BY ch.hierarchy_path"""
        params = []
    
    cursor.execute(query, params)
    categories = cursor.fetchall()
    
    # 转换为字典列表
    categories = [dict(
        id=row[0], 
        name=row[1], 
        parent_id=row[2], 
        level=row[3],
        child_count=row[4]
    ) for row in categories]
    
    return render_template('inventory_categories.html', categories=categories, search_query=search_query)

# 添加目录
@inventory_category_bp.route('/inventory_categories/add', methods=['GET', 'POST'])
def add_category():
    db = get_db()  # 添加数据库连接获取
    if request.method == 'POST':
        name = request.form['name'].strip()
        parent_id = request.form['parent_id'] or None

        if not name:
            flash('分类名称不能为空', 'danger')
            return redirect(url_for('inventory_category.add_category'))

        db = get_db()
        cursor = db.cursor()

        # 检查同级分类名称是否重复
        if parent_id:
            cursor.execute(
                'SELECT id FROM inventory_categories WHERE name = ? AND parent_id = ?',
                (name, parent_id)
            )
        else:
            cursor.execute(
                'SELECT id FROM inventory_categories WHERE name = ? AND (parent_id IS NULL OR parent_id = 0)',
                (name,)
            )

        if cursor.fetchone():
            flash('同级分类下已存在相同名称', 'danger')
            return redirect(url_for('inventory_category.add_category'))

        # 计算层级
        level = 1
        if parent_id:
            cursor.execute('SELECT level FROM inventory_categories WHERE id = ?', (parent_id,))
            parent_level = cursor.fetchone()[0]
            level = parent_level + 1
            if level > 5:
                flash('分类层级不能超过5级', 'danger')
                return redirect(url_for('inventory_category.add_category'))

        # 插入新分类
        cursor.execute(
            'INSERT INTO inventory_categories (name, parent_id, level) VALUES (?, ?, ?)',
            (name, parent_id, level)
        )
        db.commit()
        flash('碳盘查目录添加成功', 'success')
        return redirect(url_for('inventory_category.category_list'))

    # 获取父分类列表
    parents = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM inventory_categories
               WHERE parent_id IS NULL OR parent_id = 0
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM inventory_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           WHERE level < 5
           ORDER BY hierarchy_path'''
    ).fetchall()
    return render_template('inventory_add_category.html', parents=parents)

# 编辑目录
@inventory_category_bp.route('/inventory_categories/edit/<int:id>', methods=['GET', 'POST'])
def edit_category(id):
    category = get_inventory_category(id)
    if not category:
        flash('分类不存在', 'danger')
        return redirect(url_for('inventory_category.category_list'))

    if request.method == 'POST':
        name = request.form['name'].strip()
        parent_id = request.form['parent_id'] or None

        if not name:
            flash('分类名称不能为空', 'danger')
            return redirect(url_for('inventory_category.edit_category', id=id))

        # 不能选择自身作为父分类
        if parent_id and int(parent_id) == id:
            flash('不能选择自身作为父分类', 'danger')
            return redirect(url_for('inventory_category.edit_category', id=id))

        db = get_db()
        cursor = db.cursor()

        # 检查同级分类名称是否重复
        if parent_id:
            cursor.execute(
                'SELECT id FROM inventory_categories WHERE name = ? AND parent_id = ? AND id != ?',
                (name, parent_id, id)
            )
        else:
            cursor.execute(
                'SELECT id FROM inventory_categories WHERE name = ? AND (parent_id IS NULL OR parent_id = 0) AND id != ?',
                (name, id)
            )

        if cursor.fetchone():
            flash('同级分类下已存在相同名称', 'danger')
            return redirect(url_for('inventory_category.edit_category', id=id))

        # 计算层级
        level = 1
        if parent_id:
            cursor.execute('SELECT level FROM inventory_categories WHERE id = ?', (parent_id,))
            parent_level = cursor.fetchone()[0]
            level = parent_level + 1
            if level > 5:
                flash('分类层级不能超过5级', 'danger')
                return redirect(url_for('inventory_category.edit_category', id=id))

        # 更新分类
        cursor.execute(
            'UPDATE inventory_categories SET name = ?, parent_id = ?, level = ? WHERE id = ?',
            (name, parent_id, level, id)
        )
        db.commit()
        
        # 更新子分类层级
        if parent_id:
            update_child_levels(id, level)
        
        flash('碳盘查目录更新成功', 'success')
        return redirect(url_for('inventory_category.category_list'))  # 确保重定向到列表页

    # 获取父分类列表（排除当前分类及其子分类）
    db = get_db()  # 添加数据库连接获取
    parents = db.execute(
        '''WITH RECURSIVE category_hierarchy AS (
               SELECT id, name, parent_id, 
                      CAST(printf('%03d', id) AS TEXT) AS hierarchy_path, 
                      1 AS level
               FROM inventory_categories
               WHERE (parent_id IS NULL OR parent_id = 0) AND id != ?
               UNION ALL
               SELECT cc.id, cc.name, cc.parent_id, 
                      ch.hierarchy_path || '.' || printf('%03d', cc.id) AS hierarchy_path, 
                      ch.level + 1 AS level
               FROM inventory_categories cc
               JOIN category_hierarchy ch ON cc.parent_id = ch.id
               WHERE cc.id != ?
           )
           SELECT id, name, level, hierarchy_path
           FROM category_hierarchy
           WHERE level < 5 AND id NOT IN (
               WITH RECURSIVE children AS (
                   SELECT id FROM inventory_categories WHERE parent_id = ?
                   UNION ALL
                   SELECT cc.id FROM inventory_categories cc JOIN children c ON cc.parent_id = c.id
               ) SELECT id FROM children
           )
           ORDER BY hierarchy_path''',
        (id, id, id)
    ).fetchall()
    return render_template('inventory_edit_category.html', category=category, parents=parents)

# 删除目录
@inventory_category_bp.route('/inventory_categories/delete/<int:id>', methods=['POST'])
def delete_category(id):
    # 检查是否有子分类
    sub_count = get_subcategory_count(id)
    if sub_count > 0:
        flash(f'该分类下有{sub_count}个子分类，请先删除子分类', 'danger')
        return redirect(url_for('inventory_category.category_list'))

    db = get_db()
    db.execute('DELETE FROM inventory_categories WHERE id = ?', (id,))
    db.commit()
    flash('碳盘查目录删除成功', 'success')
    return redirect(url_for('inventory_category.category_list'))


# 添加更新子分类层级的函数
def update_child_levels(parent_id, new_parent_level):
    db = get_db()
    cursor = db.cursor()
    
    # 获取所有子分类
    cursor.execute('SELECT id FROM inventory_categories WHERE parent_id = ?', (parent_id,))
    children = cursor.fetchall()
    
    for child in children:
        child_id = child[0]
        new_level = new_parent_level + 1
        
        # 更新子分类层级
        cursor.execute(
            'UPDATE inventory_categories SET level = ? WHERE id = ?',
            (new_level, child_id)
        )
        
        # 递归更新孙子分类
        update_child_levels(child_id, new_level)
    db.commit()